package com.service;


import com.model.User;
import com.model.Guess;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;



public class UserService {

    private Connection connection = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;


	Guess guess = null;
	User user1 = null;

    public void setConnection() throws ClassNotFoundException, SQLException{

        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/userdb","root","root");
    }

     public void closeConnection() throws SQLException {

        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (resultSet != null) {
            resultSet.close();
        }

        if (statement != null) {
            statement.close();
        }

        if (connection != null) {
            connection.close();
        }

    }
     
	public void createGuess(Guess guess) throws SQLException {

		preparedStatement = connection.prepareStatement("INSERT INTO GUESS VALUES(?,?,?,?,?,?,?,?)");
        preparedStatement.setString(3, guess.getGuessCategory());
        preparedStatement.setString(4, guess.getGuessContent());
        preparedStatement.setString(5,guess.getGuessCreatedBy());
        preparedStatement.setString(6, guess.getGuessExpiryDate());
        preparedStatement.setInt(1, guess.getSpotOnCount());
        preparedStatement.setInt(2, guess.getDeadWrongCount());
        preparedStatement.setDate(7,guess.getCurrentDateTime());
        preparedStatement.setBoolean(8,guess.isActive());
        preparedStatement.executeUpdate();

	}

	public List<Guess> getAllGuess() throws SQLException {

         List<Guess> guessList = new ArrayList<Guess>();
         statement = connection.createStatement();
         resultSet = statement.executeQuery("SELECT * FROM GUESS");

         while(resultSet.next()){

             guess = new Guess();
             guess.setGuessCategory(resultSet.getString(3));
             guess.setGuessContent(resultSet.getString(4));
             guess.setGuessCreatedBy(resultSet.getString(5));
             guess.setGuessExpiryDate(resultSet.getString(6));
             guess.setSpotOnCount(resultSet.getInt(1));
             guess.setDeadWrongCount(resultSet.getInt(2));
             guess.setActive(resultSet.getBoolean(8));
             //guess.setCurrentDateTime(resultSet.getDate(7));

             guessList.add(guess);
         }

        
		return guessList;

	}


	public void create(User user) throws SQLException {


        preparedStatement = connection.prepareStatement("INSERT INTO USER VALUES(?,?,?,?,?,?)");
        preparedStatement.setString(1, user.getFirstName());
        preparedStatement.setString(2, user.getLastName());
        preparedStatement.setInt(3, user.getAge());
        preparedStatement.setString(4, user.getUserName());
        preparedStatement.setString(5, user.getPassword());
        preparedStatement.setBoolean(6,user.isActive());
        preparedStatement.executeUpdate();
		
	}

//        public void updateActive(User user) throws SQLException {
//
//
//        preparedStatement = connection.prepareStatement("UPDATE USER VALUES(?)");
//
//        preparedStatement.setBoolean(6,user.isActive());
//        preparedStatement.executeUpdate();
//
//	}

	public List<User> getAll() throws SQLException {

        List<User> userList = new ArrayList<User>();
        
		 statement = connection.createStatement();
         resultSet = statement.executeQuery("SELECT * FROM USER");

        while(resultSet.next()) {
            User user = new User();
            user.setFirstName(resultSet.getString(1));
            user.setLastName(resultSet.getString(2));
            user.setAge(resultSet.getInt(3));
            user.setUserName(resultSet.getString(4));
            user.setPassword(resultSet.getString(5));
            user.setActive(resultSet.getBoolean(6));
            userList.add(user);
        }


        return userList;

	}

//	public User findByUserNameAndPassword(String userName, String password) {
//
//		User user = null;
//
//		for (int i = 0; i < UserList.size(); i++) {
//
//			User user1 = UserList.get(i);
//
//			if (user1.getUserName().equals(userName)
//					&& user1.getPassword().equals(password)) {
//
//				user = user1;
//				break;
//
//			}
//
//		}
//
//		return user;
//	}

}
